package jdbc.datacheck;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 用于两库数据比对
 */
public class DataCheck {
    List<List<Object>> baosongList = new ArrayList<List<Object>>();
    List<List<Object>> fengkongList = new ArrayList<List<Object>>();

    String sql_baosong = "SELECT CFR.OUTITEMCODE,CFR.REPORTITEMCODE,to_char(CF.REPORTITEMVALUE) REPORTITEMVALUE,to_char(CF.REPORTITEMWANVALUE) REPORTITEMWANVALUE,CF.TEXTVALUE,CFR.OUTITEMTYPE,CFR.OUTITEMNOTE,CF.DESTEXT,CFRE.PORTITEMCODE,CFRE.ELEMENTCODE,CFRE.CONTEXTCODE,CFRE.UNITCODE,CFRE.DECIMALS,CFRE.STARTORENDTYPE,CT.AXIS,CT.MEMBER,CT.LINEMEMBER,CT.REMARK,CF.ISUSED FROM CFREPORTITEMCODEDESC cfr left join (SELECT * FROM CFREPORTDATA WHERE 1 =1   AND reportid = '2017_2_2_0_20170714_86010100'  AND quarter = '2'  AND month = '2017'  and  comcode='000000' and reportrate = '2' ) cf  on cfr.REPORTITEMCODE = cf.Outitemcode  left join CfReportElement cfre on cfre.portitemcode=cfr.reportitemcode left join contexttable ct on ct.contextcode=cfre.contextcode WHERE  1 = 1 and cfre.cfreporttype='0' AND cfr.halfyearreport = '1'  AND cfr.Propertyinsurance1 = '1'  ORDER BY CFR.OUTITEMCODE ASC ";
    String sql_fengkong = "SELECT CFR.OUTITEMCODE,CFR.REPORTITEMCODE,to_char(CF.REPORTITEMVALUE) REPORTITEMVALUE,to_char(CF.REPORTITEMWANVALUE) REPORTITEMWANVALUE,CF.TEXTVALUE,CFR.OUTITEMTYPE,CFR.OUTITEMNOTE,CF.DESTEXT,CFRE.PORTITEMCODE,CFRE.ELEMENTCODE,CFRE.CONTEXTCODE,CFRE.UNITCODE,CFRE.DECIMALS,CFRE.STARTORENDTYPE,CT.AXIS,CT.MEMBER,CT.LINEMEMBER,CT.REMARK,CF.ISUSED FROM CFREPORTITEMCODEDESC cfr left join (SELECT * FROM CFREPORTDATA WHERE 1 =1   AND reportid = '2017_2_2_0_20170630_86000000'  AND quarter = '2'  AND month = '2017'  and  comcode='000008' and reportrate = '2' ) cf  on cfr.REPORTITEMCODE = cf.Outitemcode  left join CfReportElement cfre on cfre.portitemcode=cfr.reportitemcode left join contexttable ct on ct.contextcode=cfre.contextcode WHERE  1 = 1 and cfre.cfreporttype='0' AND cfr.halfyearreport = '1'  AND cfr.Propertyinsurance1 = '1'  ORDER BY CFR.OUTITEMCODE ASC ";

    public static void main(String[] args) {
        DataCheck dc = new DataCheck();
        dc.queryData();
        dc.check();
    }

    public void queryData() {
        Statement s_baosong = null;
        ResultSet rs_baosong = null;

        Statement s_fengkong = null;
        ResultSet rs_fengkong = null;
        try {
            s_baosong = JDBCTools.getConnection("ltbaosong", "ltbaosong", "oracle.jdbc.OracleDriver", "jdbc:oracle:thin:@localhost:1521:orcl").createStatement();
            rs_baosong = s_baosong.executeQuery("select * from ltbaosong.cfreportelement order by id asc");

            //"select * from ltbaosong.contexttable order by contextcode asc"

            pickData(rs_baosong, baosongList);

            rs_baosong.close();
            s_baosong.close();


            s_fengkong = JDBCTools.getConnection("lttianan", "lttianan", "oracle.jdbc.OracleDriver", "jdbc:oracle:thin:@localhost:1521:orcl").createStatement();
            rs_fengkong = s_fengkong.executeQuery("select * from lttianan.cfreportelement order by id asc");

            //"select * from lttianan.contexttable order by contextcode asc"

            pickData(rs_fengkong, fengkongList);

            rs_fengkong.close();
            s_fengkong.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * 捡出数据存到List
     */
    public void pickData(ResultSet rs, List<List<Object>> list) {
        list.clear();
        int columns = 0;
        try {
            while (rs.next()) {
                columns = rs.getMetaData().getColumnCount();
                List<Object> row = new ArrayList<Object>();
                for (int i = 1; i <= columns; i++) {
                    Object o = rs.getObject(i);
                    row.add(o);
                }
                list.add(row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 数据比对
     */
    public void check() {
        Object baosong = null;
        Object fengkong = null;
        for (int i = 0; i < baosongList.size(); i++) {
            List<Object> row_baosong = baosongList.get(i);
            List<Object> row_fengkong = fengkongList.get(i);
            for (int j = 0; j < row_baosong.size(); j++) {
                baosong = row_baosong.get(j);
                fengkong = row_fengkong.get(j);
                if (baosong != fengkong && !baosong.equals(fengkong)) {
                    System.out.println("坐标：(" + i + "," + j + ")" + "\n baosong:" + baosong + " ,\n fengkong:" + fengkong);
                }
            }
        }
    }
}
